import lxml.etree
import urllib.request
import urllib.error
import pymysql
import os
import re

SOFTWARE_VERSION = '0.0.3'


#===== Functions =====
def transform_to_unified_tracks_list(inXmlPath, xslPath, xsdPath):
	print("--- xsl transformation ---")
	inXml = lxml.etree.parse(inXmlPath)
	xslt = lxml.etree.parse(xslPath)
	transform = lxml.etree.XSLT(xslt)
	outXml = transform(inXml)
	
	xsd_doc = lxml.etree.parse(xsdPath)
	xsd = lxml.etree.XMLSchema(xsd_doc)
	xsd.validate(outXml)
	print ('--- xsd validation --- ', xsd.error_log)
	
	return outXml
	
def store_tracks_to_db(url, xsl):
	print("--- writing tracks to DB ---")
	import xml.etree.ElementTree as ET
	tree = transform_to_unified_tracks_list(url, xsl, os.path.join('..', '3.XML', 'tracks_output.xsd'))
	root = tree.getroot()
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013', charset='utf8')
	cur = conn.cursor()
	print("--- saving data ---")
	for track in root.findall('track'):
		name = track.find('artistName').text
		statement = "call add_artist ('" + re.escape(name) + "');"
		cur.execute(statement)
		conn.commit()
		track_name = track.find('name').text
		image_url = track.find('image').text
		if image_url is None:
			image_url = "http://a2.twimg.com/profile_images/1307896048/thumbs-up-vault-boy-1_reasonably_small.jpg"
		statement = "INSERT INTO `tracks` (track_name, artist_id, cover) VALUES ('" + re.escape(track_name) + "', (select `id` from `artists` where `name` like '" + re.escape(name) + "'), '" + re.escape(image_url) + "');"
		cur.execute(statement)
	cur.close()
	conn.commit()
	conn.close()
	return cur.rowcount
	
def store_supplementary_data_to_db():
	print("--- writing supplementary data to DB ---")
	conn = pymysql.connect(host='mysql2.alwaysdata.com', port=3306, user='epamtest', passwd='e123456;', db='epamtest_q42013', charset='utf8')
	cur = conn.cursor()
	statement = "INSERT INTO `supplementary` (software_version, os, username) VALUES ('" + str(SOFTWARE_VERSION) + "', '" + str(os.name) + "', '" + os.getlogin() + "');"
	cur.execute(statement)
	cur.close()
	conn.commit()
	conn.close()
	return cur.rowcount

#===== Do transformations =====
#print("Last.Fm: ", transform_to_unified_tracks_list("http://ws.audioscrobbler.com/2.0/?method=chart.gettoptracks&api_key=a85bb343adad2aa329106a81805fad9b", "..\\3.XML\\lastFMTopTracks.xsl", "..\\3.XML\\tracks_output.xsd"))
#transform_to_unified_tracks_list("http://ws.audioscrobbler.com/2.0/?method=chart.gettoptracks&api_key=a85bb343adad2aa329106a81805fad9b", "..\\3.XML\\lastFMTopTracks.xsl", "..\\3.XML\\tracks_output.xsd")
#transform_to_unified_tracks_list("http://ws.audioscrobbler.com/2.0/?method=chart.gettoptracks&api_key=a85bb343adad2aa329106a81805fad9b", "..\\3.XML\\airplay.xsl", "..\\3.XML\\tracks_output.xsd")
#===== Store to database =====
store_supplementary_data_to_db()
print("--- Last.Fm ---")
store_tracks_to_db("http://ws.audioscrobbler.com/2.0/?method=chart.gettoptracks&api_key=a85bb343adad2aa329106a81805fad9b", os.path.join('..', '3.XML', 'lastFMTopTracks.xsl'))
print("--- Itunes ---")
store_tracks_to_db("http://itunes.apple.com/us/rss/topsongs/limit=200/xml", os.path.join('..', '3.XML', 'Itunes.xsl'))